﻿﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Dapper;
using System.Configuration;
using Base.Util;
using Base.BLL;
using System.Reflection;
using System.Text.RegularExpressions;

namespace Base.DAL
{
    /// <summary>
    /// 数据库CUID操作类
    /// </summary>
    class Db
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string StrConn
        {
            get
            {
                return ConfigService.GetIni("ConnectionString",
                    ConfigurationManager.AppSettings["ConnectionString"]);
                //"Data Source=.;Initial Catalog=KJ440KY;Integrated Security=True;MultipleActiveResultSets=True");
            }
        }
        /// <summary>
        /// master数据库连接字符串
        /// </summary>
        public static string StrConnMaster
        {
            get
            {
                return Regex.Replace(StrConn, "Initial Catalog=(.+?);", "Initial Catalog=master;");
            }
        }
        /// <summary>
        /// 数据库名
        /// </summary>
        public static string StrDbName
        {
            get
            {
                string tbName = "KJ440KY";
                GroupCollection matches = Regex.Match(StrConn, "Initial Catalog=(.+?);").Groups;
                if (matches.Count > 1)
                {
                    tbName = matches[1].Value;
                }
                return tbName;
            }
            set
            {
                string newConn = Regex.Replace(StrConn, "Initial Catalog=(.+?);", string.Format("Initial Catalog={0};", value));
                ConfigService.SetIni("ConnectionString", newConn);
            }
        }
        /// <summary>
        /// 返回一个数据库连接
        /// </summary>
        public static IDbConnection Conn
        {
            get
            {
                return new SqlConnection(StrConn);
            }
        }
        /// <summary>
        /// 根据实体类获取插入sql预编译语句
        /// </summary>
        /// <param name="e">实体类</param>
        /// <returns>插入sql预编译语句，自增列不在插入字段中</returns>
        public static string SqlInsert(IEntity e)
        {
            string sql = "INSERT INTO " + e.Tname + "(";
            string cols = "";
            string values = "";
            bool isAuto = e.ColIsAuto[0] == 1;
            bool isFirst = true;
            for (int i = 0; i < e.Cols.Length; i++)
            {
                if (e.ColIsAuto[i] == 1)
                {
                    continue;
                }
                if (isFirst)
                {
                    cols += e.Cols[i];
                    values += "@" + e.Cols[i];
                }
                else
                {
                    cols += "," + e.Cols[i];
                    values += ",@" + e.Cols[i];
                }
                isFirst = false;
            }
            sql += cols + ") VALUES(" + values + ");";
            if (isAuto)
            {
                sql += "SELECT CAST(SCOPE_IDENTITY() as int)";
            }
            return sql;
        }
        /// <summary>
        /// 插入一个实体对象
        /// </summary>
        /// <param name="e">要插入的实体对象</param>
        /// <returns>如果主键为自增字段，则返回插入成功后的行主键值，否则返回0</returns>
        public static int Insert(IEntity e)
        {
            if (e.Cols.Length == 0)
            {
                return 0;
            }
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                conn.Open();
                string sql = SqlInsert(e);
                return conn.Query<int>(sql, e).FirstOrDefault();
            }
            //TODO:插入后进行相关统计计算
        }
        /// <summary>
        /// 插入多个实体对象
        /// </summary>
        /// <param name="es">要插入的实体列表</param>
        /// <returns>返回插入成功个数</returns>
        public static int Insert<T>(List<T> es) where T : IEntity
        {
            if (es.Count == 0)
            {
                return 0;
            }
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();
                string sql = SqlInsert(es[0]);
                try
                {
                    int rst = conn.Execute(sql, es, trans);
                    trans.Commit();
                    return rst;
                }
                catch (Exception e)
                {
                    trans.Rollback();
                    LoggerHelper.Error(e.StackTrace);
                    return 0;
                }
            }
            //TODO:插入后进行相关统计计算
        }

        /// <summary>
        /// 根据实体类获取删除sql预编译语句
        /// </summary>
        /// <param name="e">实体类</param>
        /// <returns>删除sql预编译语句，若有主键，则按主键匹配，否则按全值匹配</returns>
        public static string SqlDelete(IEntity e)
        {
            string sql = "DELETE FROM " + e.Tname + " WHERE ";
            bool hasKey = e.ColIsPK[0] == 1;
            bool isFirst = true;
            for (int i = 0; i < e.Cols.Length; i++)
            {
                if (hasKey && e.ColIsPK[i] == 0)
                {
                    continue;
                }
                if (isFirst)
                {
                    sql += e.Cols[i] + "=@" + e.Cols[i];
                }
                else
                {
                    sql += " AND " + e.Cols[i] + "=@" + e.Cols[i];
                }
                isFirst = false;
            }
            return sql;
        }
        /// <summary>
        /// 删除实体类
        /// </summary>
        /// <param name="e">要删除的实体类</param>
        /// <returns>返回操作影响的行数</returns>
        public static int Delete(IEntity e)
        {
            if (e.Cols.Length == 0)
            {
                return 0;
            }
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                conn.Open();
                string sql = SqlDelete(e);
                return conn.Execute(sql, e);
            }
        }
        /// <summary>
        /// 删除多个实体对象
        /// </summary>
        /// <param name="es">要删除的实体列表</param>
        /// <returns>返回操作影响的行数</returns>
        public static int Delete(List<IEntity> es)
        {
            if (es.Count == 0)
            {
                return 0;
            }
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();
                string sql = SqlDelete(es[0]);
                try
                {
                    int rst = conn.Execute(sql, es, trans);
                    trans.Commit();
                    return rst;
                }
                catch (Exception e)
                {
                    trans.Rollback();
                    LoggerHelper.Error(e.StackTrace);
                    return 0;
                }
            }
        }
        /// <summary>
        /// 清空表
        /// </summary>
        /// <param name="tname">表名</param>
        /// <returns>返回操作影响的行数</returns>
        public static int Clear(string tname)
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                conn.Open();
                string sql = "DELETE FROM " + tname;
                return conn.Execute(sql);
            }
        }
        /// <summary>
        /// 获取实体更新sql语句
        /// 如无主键，则抛出异常
        /// </summary>
        /// <param name="e">实体</param>
        /// <returns>实体更新sql语句。更新字段不包括自增列，更新条件采用主键</returns>
        public static string SqlUpdate(IEntity e)
        {
            string sql = "UPDATE " + e.Tname + " SET ";
            string where = " WHERE ";
            bool hasKey = e.ColIsPK[0] == 1;
            if (!hasKey)
            {
                LoggerHelper.Error("试图更新没有主键的表" + e.Tname);
                throw new Exception("没有主键，无法更新");
            }
            bool isFirstPK = true;
            bool isFirstAuto = true;
            for (int i = 0; i < e.Cols.Length; i++)
            {
                if (e.ColIsPK[i] == 1)
                {
                    if (isFirstPK)
                    {
                        where += e.Cols[i] + "=@" + e.Cols[i];
                    }
                    else
                    {
                        where += "," + e.Cols[i] + "=@" + e.Cols[i];
                    }
                    isFirstPK = false;
                }
                if (e.ColIsAuto[i] == 0)
                {
                    if (isFirstAuto)
                    {
                        sql += e.Cols[i] + "=@" + e.Cols[i];
                    }
                    else
                    {
                        sql += "," + e.Cols[i] + "=@" + e.Cols[i];
                    }
                    isFirstAuto = false;
                }
            }
            sql += where;
            return sql;
        }

        /// <summary>
        /// 更新实体类
        /// </summary>
        /// <param name="e">要更新的实体类</param>
        /// <returns>返回操作影响的行数</returns>
        public static int Update(IEntity e)
        {
            if (e.Cols.Length == 0)
            {
                return 0;
            }
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                conn.Open();
                string sql = SqlUpdate(e);
                return conn.Execute(sql, e);
            }
        }
        /// <summary>
        /// 删除多个实体对象
        /// </summary>
        /// <param name="es">要删除的实体列表</param>
        /// <returns>返回操作影响的行数</returns>
        public static int Update<T>(List<T> es) where T : IEntity
        {
            if (es.Count == 0)
            {
                return 0;
            }
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();
                string sql = SqlUpdate(es[0]);
                try
                {
                    int rst = conn.Execute(sql, es, trans);
                    trans.Commit();
                    return rst;
                }
                catch (Exception e)
                {
                    trans.Rollback();
                    LoggerHelper.Error(e.StackTrace);
                    return 0;
                }
            }
        }

        /// <summary>
        /// 获取查询语句
        /// </summary>
        /// <param name="e">数据库实体</param>
        /// <returns>查询sql语句</returns>
        public static string SqlQuery(IEntity e)
        {
            return "SELECT " + e.Tname + "." + string.Join("," + e.Tname + ".", e.Cols) + " FROM " + e.Tname;
        }
        /// <summary>
        /// 获取查询语句（仅1条）
        /// </summary>
        /// <param name="e">数据库实体</param>
        /// <returns>查询sql语句</returns>
        public static string SqlQueryOne(IEntity e)
        {
            return "SELECT TOP 1 " + e.Tname + "." + string.Join("," + e.Tname + ".", e.Cols) + " FROM " + e.Tname;
        }
        /// <summary>
        /// 泛型查询方法
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="sql">查询sql语句</param>
        /// <returns>返回查询结果</returns>
        public static IEnumerable<T> Query<T>(string sql)
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                try
                {
                    conn.Open();
                    return conn.Query<T>(sql);
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex);
                    return new List<T>();
                }
            }
        }

        /// <summary>
        /// 泛型查询方法
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="sql">查询sql语句</param>
        /// <param name="param">查询参数</param>
        /// <returns>返回查询结果</returns>
        public static IEnumerable<T> Query<T>(string sql, object param)
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                try
                {
                    conn.Open();
                    return conn.Query<T>(sql, param);
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex);
                    return new List<T>();
                }
            }
        }
        /// <summary>
        /// 泛型查询方法
        /// </summary>
        /// <typeparam name="T">实体类，必须实现IEntity接口并有无参构造方法</typeparam>
        /// <returns>返回查询结果</returns>
        public static IEnumerable<T> Query<T>() where T : IEntity, new()
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                try
                {
                    conn.Open();
                    string sql = "SELECT * FROM " + (new T().Tname);
                    return conn.Query<T>(sql);
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex);
                    return new List<T>();
                }
            }
        }
        /// <summary>
        /// 查询指定存储过程
        /// </summary>
        /// <typeparam name="T">查询返回类型</typeparam>
        /// <param name="sql">要查询的存储过程</param>
        /// <param name="obj">参数值</param>
        /// <returns>返回查询记录集</returns>
        public static IEnumerable<T> QuerySP<T>(string sql, object obj)
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                try
                {
                    conn.Open();
                    return conn.Query<T>(sql, obj, commandType: CommandType.StoredProcedure);
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex);
                    return new List<T>();
                }
            }
        }
        /// <summary>
        /// 泛型条件查询方法
        /// </summary>
        /// <typeparam name="T">实体类，必须实现IEntity接口并有无参构造方法</typeparam>
        /// <param name="where">查询sql语句，若为null</param>
        /// <returns>返回查询结果</returns>
        public static IEnumerable<T> QueryWhere<T>(string where = "1=1", object param = null) where T : IEntity, new()
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                string err = "Conn failed.";
                try
                {
                    conn.Open();
                    string sql = SqlQuery(new T()) + " WHERE " + where;
                    err = "Query failed:"+sql;
                    return conn.Query<T>(sql, param);
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex);
                    LoggerHelper.Error(err);
                    return new List<T>();
                }
            }
        }
        /// <summary>
        /// 根据条件获取一个实例
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="param">查询参数</param>
        public static T Find<T>(string sql, object param = null)
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                try
                {
                    conn.Open();
                    return conn.Query<T>(sql, param).FirstOrDefault<T>();
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex);
                    return default(T);
                }
            }
        }
        /// <summary>
        /// 查找第一行数据
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <returns>返回实体类对应表的第一行数据</returns>
        public static T Find<T>() where T : IEntity, new()
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                try
                {
                    conn.Open();
                    return conn.Query<T>(SqlQueryOne(new T())).FirstOrDefault<T>();
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex);
                    return default(T);
                }
            }
        }
        /// <summary>
        /// 按照主键查找数据库实体类
        /// </summary>
        /// <typeparam name="T">数据库实体类</typeparam>
        /// <param name="keys">查询参数，形式类似{ID=1}</param>
        /// <returns>返回查找到的值</returns>
        public static T Find<T>(object keys) where T : IEntity, new()
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                conn.Open();
                T e = new T();
                string sql = SqlQueryOne(e) + " WHERE 1=1";
                for (int i=0;i<e.ColIsPK.Length;i++)
                {
                    if(e.ColIsPK[i]==1)
                        sql += " AND " + e.Cols[i] + "=@" + e.Cols[i];
                }
                try
                {
                    return conn.Query<T>(sql, keys).FirstOrDefault<T>();
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex);
                    return default(T);
                }
            }
        }
        /// <summary>
        /// 泛型分页查询方法
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="sql">查询sql语句</param>
        /// <param name="page">页序号，从0开始</param>
        /// <param name="pagecount">每页数量</param>
        /// <returns>返回查询结果</returns>
        public static IEnumerable<T> QueryPage<T>(int page, int pagecount) where T : IEntity, new()
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                try
                {
                    conn.Open();
                    T t = new T();
                    string sql = "SELECT * FROM( SELECT ROW_NUMBER() OVER(ORDER by ID) rowid,* FROM " +
                        t.Tname + ")Tb WHERE Tb.rowid between " + (page * pagecount + 1) + " AND " + (page * pagecount + pagecount);
                    return conn.Query<T>(sql);
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex);
                    return new List<T>();
                }
            }
        }
        /// <summary>
        /// 执行指定sql语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="sql">参数</param>
        /// <returns>返回操作影响的行数</returns>
        public static int Execute(string sql, object param = null)
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                conn.Open();
                return conn.Execute(sql, param);
            }
        }
        /// <summary>
        /// 执行指定存储过程
        /// </summary>
        /// <param name="sql">要执行的存储过程</param>
        /// <param name="obj">参数值</param>
        /// <returns>返回影响的记录数</returns>
        public static int ExecuteSP(string sql, object obj)
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                conn.Open();
                return conn.Execute(sql, obj, commandTimeout: 0, commandType: CommandType.StoredProcedure);
            }
        }
        /// <summary>
        /// 执行指定sql语句
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">要执行的sql语句</param>
        /// <param name="es">参数列表</param>
        /// <returns>返回影响的记录集个数</returns>
        public static int Execute<T>(string sql, List<T> es)
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                conn.Open();
                return conn.Execute(sql, es);
            }
        }

        /// <summary>
        /// 获取符合条件的实体数
        /// </summary>
        /// <typeparam name="T">泛型，应实现IEntity接口</typeparam>
        /// <param name="where">查询条件</param>
        /// <returns>返回符合条件的实体数</returns>
        public static int Count<T>(string where = "1=1") where T : IEntity, new()
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                conn.Open();
                T t = new T();
                string sql = "SELECT COUNT(1) FROM " + t.Tname + " WHERE " + where;
                return conn.Query<int>(sql).FirstOrDefault();
            }
        }
        /// <summary>
        /// 获取sql语句对应的记录数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <returns>返回sql语句对应的记录数</returns>
        public static int Count(string sql,object param=null)
        {
            using (IDbConnection conn = new SqlConnection(StrConn))
            {
                try
                {
                    conn.Open();
                    return conn.Query<int>(sql, param).FirstOrDefault();
                }
                catch (Exception ex)
                {
                    LoggerHelper.Error(ex);
                    return 0;
                }
            }
        }
        /// <summary>
        /// 按属性名称复制一个实体的值到另一个实体(浅拷贝)
        /// </summary>
        /// <param name="from">源</param>
        /// <param name="to">目的</param>
        public static void Cpy(IEntity from, IEntity to)
        {
            Type tFrom = from.GetType();
            Type tTo = to.GetType();
            foreach (string col in to.Cols)
            {
                if (from.Cols.Contains(col))
                {
                    var v = tFrom.GetProperty(col).GetValue(from, null);
                    if (v != null)
                    {
                        tTo.InvokeMember(col, BindingFlags.SetProperty, null, to, new object[] { v });
                    }
                }
            }
        }
        public static void Test()
        {
            /*_Users user = new _Users
            {
                ID = 1,
                Email = "qq.com",
                Address = "安徽",
                UserName = "jack2"
            };
            List<IEntity> us = new List<IEntity>();
            us.Add(user);
            _Users user2 = user.Clone();
            user2.ID = 2;
            us.Add(user2);
            //Console.WriteLine(Db.Insert(us));
            //Console.WriteLine(Db.Delete(us));
            //Console.WriteLine(Db.Update(us));
            //foreach (var u in Db.Query<_Users>("SELECT * FROM _Users"))
            //{
            //    Console.WriteLine(u.UserName);
            //}
            //Console.WriteLine(Db.Find<_Users>("ID=2").UserName);
            Console.WriteLine(Db.Count<_Users>());
            foreach (var u in Db.QueryPage<_Users>(1, 2))
            {
                Console.WriteLine(u.UserName);
            }*/
            string sql = Db.SqlQuery(new ZCSJB());
            Console.WriteLine();
        }
    }
}
